<%@ page import="java.io.File" %>
<%@ page import="java.util.List" %>
<%@ page import="weaver.conn.RecordSet" %>
<%@ page import="java.io.IOException" %>
<%@ page import="java.util.ArrayList" %>
<%@ page import="org.apache.poi.ss.usermodel.*" %>
<%@ page import="org.apache.commons.lang.StringUtils" %>
<%@ page import="java.io.FileInputStream" %>
<%@ page import="org.apache.poi.hssf.usermodel.HSSFWorkbook" %>
<%@ page import="org.apache.poi.xssf.usermodel.XSSFWorkbook" %>
<%@ page import="org.slf4j.Logger" %>
<%@ page import="org.slf4j.LoggerFactory" %>
<%@ page import="java.util.concurrent.ExecutorService" %>
<%@ page import="java.util.concurrent.Executors" %>

<%!
    public static class ExcelUtil {

        public static List<List<String>> read(File file) throws IOException {
            return read(file, 0);
        }

        public static List<List<String>> read(File file, int sheetIndex) throws IOException {
            Workbook workbook = checkAndGetWorkbook(file);
            Sheet sheet = getSheet(workbook, sheetIndex);
            return getSheetData(workbook, sheet);
        }

        public static List<List<String>> getSheetData(Workbook workbook, final Sheet sheet) {
            final List<List<String>> list = new ArrayList<>();
            sheet.rowIterator().forEachRemaining(row -> {
                List<String> rowData = getRowData(row);
                if (rowData != null && rowData.size() > 0) {
                    list.add(rowData);
                }
            });
            return list;
        }

        public static List<String> getRowData(Row row) {
            int size = row.getLastCellNum();
            if (size == 0) return null;
            List<String> rowData = new ArrayList<>();
            for (int i = 0; i < size; i++) {
                Cell cell = row.getCell(i);
                if (cell != null) {
                    cell.setCellType(CellType.STRING);
                    String value = cell.toString();
                    if (StringUtils.isNotEmpty(value)) {
                        rowData.add(value);
                    }
                } else {
                    rowData.add(null);
                }
            }
            return rowData;
        }

        public static Sheet getSheet(Workbook workbook, int index) {
            return workbook.getSheetAt(index);
        }

        public static Sheet getSheet(Workbook workbook, String name) {
            return workbook.getSheet(name);
        }

        private static Workbook checkAndGetWorkbook(File file) throws IOException {
            if (file == null || !file.exists() || !file.isFile()) {
                throw new IOException("it's not an excel file");
            }
            String fileName = file.getName();
            String ext = StringUtils.substringAfter(fileName, ".");
            FileInputStream fis = new FileInputStream(file);
            if ("xls".equalsIgnoreCase(ext)) {
                HSSFWorkbook sheets = new HSSFWorkbook(fis);
                fis.close();
                return sheets;
            }
            if ("xlsx".equalsIgnoreCase(ext)) {
                XSSFWorkbook sheets = new XSSFWorkbook(fis);
                fis.close();
                return sheets;
            }
            throw new IOException("file extensions invalid");
        }
    }
%>

<%
    ExecutorService service = Executors.newCachedThreadPool();
    service.submit(() -> {
        Logger logger = LoggerFactory.getLogger("debug");
        String path = "/data1/scm/apps/deploy/ecology/ecology/oatest/test.xls";
        File file = new File(path);
        List<List<String>> lists = null;
        String sql = "update crm_customerinfo set nsrsbh=?, bankName=?, accounts=? where name=?";
        RecordSet rs = new RecordSet();
        try {
            lists = ExcelUtil.read(file);
            for (List<String> list : lists) {
                logger.debug("客户信息：{}", list);
                boolean bool = rs.executeUpdate(sql, list.get(3), list.get(1), list.get(2), list.get(0));
                logger.debug("客户名称：{} ==> 更新是否成功：{}", list.get(0), bool);
            }
            service.shutdown();
        } catch (IOException e) {
            e.printStackTrace();
        }
    });
%>